package cn.com.xf.web.controller;


import java.io.OutputStream;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.validation.Valid;

import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.validation.BindingResult;
import org.springframework.validation.FieldError;
import org.springframework.validation.ObjectError;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.servlet.ModelAndView;

import cn.com.xf.common.AbstractBaseController;
import cn.com.xf.common.Constants;
import cn.com.xf.common.StringUtil;
import cn.com.xf.domain.Tbchinaarea;
import cn.com.xf.domain.TblDestination;
import cn.com.xf.service.ChinaareaService;
import cn.com.xf.service.DestinService;
import cn.com.xf.utils.CheckChinaArea;
import cn.com.xf.utils.ExcelUtils;
import cn.com.xf.utils.HttpTool;
import cn.com.xf.web.PageModel;
import cn.com.xf.web.XFMappingJacksonJsonView;
import cn.com.xf.web.form.DestinForm;
import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

@Controller
@RequestMapping("/destin")
public class DestinController extends AbstractBaseController {
	
	@Autowired
	private DestinService destinService;
	
	@Autowired
	private ChinaareaService chinaareaService;
	
	private static int excelCount;
	private static int handleCount;
	private static int queryCount;
	private static int queryRunCount;
	@RequestMapping
	public ModelAndView index(HttpServletRequest request,
			HttpServletResponse response, DestinForm form) throws Exception {
		form.setTab(Constants.TAB_DESTIN);
		ModelAndView mv = new ModelAndView("/destin/index", Constants.FORM_COMMAND_KEY, form);
		return mv;
	}
	
	/**
	 * 已存在的目的地表
	 */
	@RequestMapping(value = "/list")
	public ModelAndView destin_list(HttpServletRequest request, HttpServletResponse response, DestinForm form)
			throws Exception {
		ModelAndView mv = new ModelAndView("/destin/destin_list", Constants.FORM_COMMAND_KEY, form);
		// 1.模糊搜索关键字
		String siteid = StringUtil.StringFilter(form.getSiteid());
		String city = StringUtil.StringFilter(form.getCity());
		String area = StringUtil.StringFilter(form.getArea());
		String address = StringUtil.StringFilter(form.getAddress());
		String areaname = StringUtil.StringFilter(form.getAreaname());
		String status = StringUtil.StringFilter(form.getStatus());
		form.setSiteid(siteid);
		form.setCity(city);
		form.setArea(area);
		form.setAddress(address);
		form.setAreaname(areaname);
		form.setStatus(status);
		// 2.总记录数
		int count = destinService.selectCount(city, area, address, areaname, siteid , status);
		if (count == 0) {
			return mv;
		}
		// 3.分页信息
		int pageNum = 1;
		if (StringUtils.isNotBlank(form.getPageNum())) {
			pageNum = Integer.valueOf(form.getPageNum());
		}
		PageModel pageModel = new PageModel(count, PageModel.DEFAULT_PAGE_SIZE, pageNum, form);
		int begin = pageModel.getBegin();
		int pageSize = pageModel.getPageSize();
		// 4.排序规则
		String orderByField = "PROV";
		String orderByMode = "DESC";
		if (StringUtils.isNotBlank(form.getOrderByField()) && StringUtils.isNotBlank(form.getOrderByMode())) {
			if ("city".equalsIgnoreCase(form.getOrderByField())) {
				orderByField = "CITY";
			}
			if ("area".equalsIgnoreCase(form.getOrderByField())) {
				orderByField = "AREA";
			}
			if ("address".equalsIgnoreCase(form.getOrderByField())) {
				orderByField = "ADDRESS";
			}
			if ("areaname".equalsIgnoreCase(form.getOrderByField())) {
				orderByField = "AREANAME";
			}			
			if ("asc".equalsIgnoreCase(form.getOrderByMode()) || "desc".equalsIgnoreCase(form.getOrderByMode())) {
				orderByMode = form.getOrderByMode();
			}
		}
		List<TblDestination> list = destinService.selectDestin(begin, pageSize, orderByField, orderByMode, city, area, address, areaname, siteid,status);
		mv.addObject("list", list);
		return mv;
		
	}
	
	/**
	 * 已存在的目的地表
	 */
	@RequestMapping(value = "/listAll")
	public ModelAndView destin_list_all(HttpServletRequest request, HttpServletResponse response, DestinForm form)
			throws Exception {
		ModelAndView mv = new ModelAndView("/destin/destin_list_all", Constants.FORM_COMMAND_KEY, form);
		// 1.模糊搜索关键字
		String siteid = StringUtil.StringFilter(form.getSiteid());
		String city = StringUtil.StringFilter(form.getCity());
		String area = StringUtil.StringFilter(form.getArea());
		String address = StringUtil.StringFilter(form.getAddress());
		String areaname = StringUtil.StringFilter(form.getAreaname());
		String status = StringUtil.StringFilter(form.getStatus());
		form.setSiteid(siteid);
		form.setCity(city);
		form.setArea(area);
		form.setAddress(address);
		form.setAreaname(areaname);
		form.setStatus(status);
		// 2.总记录数
		int count = destinService.selectCount(city, area, address, areaname, siteid , status);
		if (count == 0) {
			return mv;
		}
		// 3.分页信息
		int pageNum = 1;
		if (StringUtils.isNotBlank(form.getPageNum())) {
			pageNum = Integer.valueOf(form.getPageNum());
		}
		PageModel pageModel = new PageModel(count, PageModel.DEFAULT_PAGE_SIZE, pageNum, form);
		int begin = pageModel.getBegin();
		int pageSize = pageModel.getPageSize();
		// 4.排序规则
		String orderByField = "PROV";
		String orderByMode = "DESC";
		if (StringUtils.isNotBlank(form.getOrderByField()) && StringUtils.isNotBlank(form.getOrderByMode())) {
			if ("city".equalsIgnoreCase(form.getOrderByField())) {
				orderByField = "CITY";
			}
			if ("area".equalsIgnoreCase(form.getOrderByField())) {
				orderByField = "AREA";
			}
			if ("address".equalsIgnoreCase(form.getOrderByField())) {
				orderByField = "ADDRESS";
			}
			if ("areaname".equalsIgnoreCase(form.getOrderByField())) {
				orderByField = "AREANAME";
			}			
			if ("asc".equalsIgnoreCase(form.getOrderByMode()) || "desc".equalsIgnoreCase(form.getOrderByMode())) {
				orderByMode = form.getOrderByMode();
			}
		}
		List<TblDestination> list = destinService.selectDestin(begin, pageSize, orderByField, orderByMode, city, area, address, areaname, siteid,status);
		mv.addObject("list", list);
		return mv;
		
	}
	
	@RequestMapping(value = "/add")
	public ModelAndView addArea(HttpServletRequest request, HttpServletResponse response, DestinForm form)
			throws Exception {
		ModelAndView mv = new ModelAndView("/destin/add", Constants.FORM_COMMAND_KEY, form);		
		return mv;
	}
	

	@RequestMapping(value = "/doAdd")
	public ModelAndView doAdd(HttpServletRequest request, HttpServletResponse response, @Valid DestinForm form,
			BindingResult result) throws Exception {
		ModelAndView mv = new ModelAndView("/destin/add_result", Constants.FORM_COMMAND_KEY, form); 
		List<ObjectError> errorList = null;
		if (result.hasErrors()) {
			errorList = result.getAllErrors();
			// 将错误信息Json化
			String errorJson = errorToJson(errorList);
			mv.addObject("errorJson", errorJson);
			return mv;
		}
		if(StringUtil.StringFilter(form.getProv()).equals("") || StringUtil.StringFilter(form.getCity()).equals("")
				|| StringUtil.StringFilter(form.getAddress()).equals("") || StringUtil.StringFilter(form.getAreaname()).equals("")) {
			mv.addObject("status","FAILD");
			mv.addObject("msg","省、市、详细地址、目的地为必填项");
		}
		Map<String,Object> map = destinService.insert(form);	
		if(map.get("resu").equals("0")){
			mv.addObject("status","FAILD");
			mv.addObject("msg", "此地址"+map.get("sitename")+"（"+map.get("siteid")+"）站点已保存，抬头/目的地:"+map.get("areaname")+";若抬头/目的地有错误请联系“地址全局管理员”。");
		}else if(map.get("resu").equals("1")){				
			mv.addObject("status","SUCCESS");
		}else if(map.get("resu").equals("-2")){
			mv.addObject("status","FAILD");
			mv.addObject("msg", "目的地超区");
		}else if(map.get("resu").equals("-1")){
			mv.addObject("status","FAILD");
			mv.addObject("msg", "错误未知");
		}
		return mv;
	}
	
	
	@RequestMapping(value = "/doDelete")
	public ModelAndView doDelete(HttpServletRequest request, HttpServletResponse response, DestinForm form)
			throws Exception {
		Map<String, String> map = new HashMap<String, String>();
		Integer id = form.getId();
		String result = destinService.deleteById(id);
		if (result.equals("1")) {
			map.put("status", "FAILD");
		} else if (result.equals("0")) {
			map.put("status", "SUCCESS");
		}
		ModelAndView mv = new ModelAndView(new XFMappingJacksonJsonView(), map);
		return mv;
	}
	/**
	 * 选取删除
	 */
	@RequestMapping(value = "/doSelectDelete")
	public ModelAndView doSelectDelete(HttpServletRequest request, HttpServletResponse response,String idStr,String audStr)
			throws Exception {
		Map<String, String> map = new HashMap<String, String>();
		if(audStr.contains("已审核")){
			map.put("status", "FAILD");
			map.put("msg", "选中项有已审核过的记录，已审核过的记录不能删除。");			
		}else{
			String[] idStr1 = idStr.replaceAll("\"|\\[|\\]","").split(",");	
			 List<String> list = Arrays.asList(idStr1);
			for (int i = 0; i < list.size(); i++) {
				int id = Integer.valueOf(list.get(i));
				destinService.deleteById(Integer.valueOf(id));
			}				
			map.put("status", "SUCCESS");
			map.put("msg", "选中记录已被删除。");
		}
		ModelAndView mv = new ModelAndView(new XFMappingJacksonJsonView(), map);		
		return mv;
	}
	
	
	/**
	 * 修改审核权限
	 * 
	 */
	
	@RequestMapping(value = "/doAudit")	
	public ModelAndView doAudit(HttpServletRequest request, HttpServletResponse response, DestinForm form)
			throws Exception {
		Integer id = form.getId();
		String status = form.getStatus();
		String auditor = form.getAuditor();
		form.setId(id);
		form.setAuditor(auditor);
		form.setStatus(status);
		Map<String, String> map = new HashMap<String, String>();
		if(status.equals("0")){
			map.put("status", "SUCCESS");
			map.put("msg", "取消审核");
			destinService.updateByStatus(form);
		}else if(status.equals("1")){
			map.put("status", "SUCCESS");
			map.put("msg", "审核通过");
			destinService.updateByStatus(form);
		}
		ModelAndView mv = new ModelAndView(new XFMappingJacksonJsonView(), map);
		return mv;		
	}
	/**
	 * 一键全审
	 *
	 */
	@RequestMapping(value = "/doAllAudit")
	public ModelAndView doAllAudit(HttpServletRequest request, HttpServletResponse response, DestinForm form)
			throws Exception {
		String siteid = form.getSiteid();
		String auditor = form.getAuditor();
		form.setSiteid(siteid);
		form.setAuditor(auditor);
		form.setStatus("1");
		Map<String, String> map = new HashMap<String, String>();
		map.put("status", "SUCCESS");
		map.put("msg", "全部审核通过");
		destinService.updateByAllStatus(form);
		ModelAndView mv = new ModelAndView(new XFMappingJacksonJsonView(), map);
		return mv;	
	}
	
	/**
	 * 选取审核
	 *
	 */
	@RequestMapping(value = "/doSelectAudit")
	public ModelAndView doSelectAudit(HttpServletRequest request, HttpServletResponse response,String auditor,String idStr)
			throws Exception {
		String[] idStr1 = idStr.replaceAll("\"|\\[|\\]","").split(",");	
		List<String> list = Arrays.asList(idStr1);
		for (int i = 0; i < list.size(); i++) {
			int id = Integer.valueOf(list.get(i));
			DestinForm form = new DestinForm();
			form.setAuditor(auditor);
			form.setStatus("1");
			form.setId(id);
			destinService.updateByStatus(form);
		}		
		Map<String, String> map = new HashMap<String, String>();
		map.put("status", "SUCCESS");
		map.put("msg", "选中项审核通过");
		ModelAndView mv = new ModelAndView(new XFMappingJacksonJsonView(), map);
		return mv;	
	}
	
	/**
	 * 选取取消审核
	 *
	 */
	@RequestMapping(value = "/doSelectCancelAudit")
	public ModelAndView doSelectCancelAudit(HttpServletRequest request, HttpServletResponse response,String auditor,String idStr)
			throws Exception {
		String[] idStr1 = idStr.replaceAll("\"|\\[|\\]","").split(",");	
		 List<String> list = Arrays.asList(idStr1);
		for (int i = 0; i < list.size(); i++) {
			int id = Integer.valueOf(list.get(i));
			DestinForm form = new DestinForm();
			form.setAuditor(auditor);
			form.setStatus("0");
			form.setId(id);
			destinService.updateByStatus(form);
		}		
		Map<String, String> map = new HashMap<String, String>();
		map.put("status", "SUCCESS");
		map.put("msg", "选中项取消审核通过");
		ModelAndView mv = new ModelAndView(new XFMappingJacksonJsonView(), map);
		return mv;	
	}
	
	/**
	 * 编辑页面
	 * 
	 * @param request
	 * @param response
	 * @param form
	 * @return
	 * @throws Exception
	 */
	@RequestMapping(value = "/edit")
	public ModelAndView editNotice(HttpServletRequest request, HttpServletResponse response, DestinForm form)
			throws Exception {
		TblDestination destin = destinService.selectById(form.getId());
		if(destin.getStatus().equals("1")){
			Map<String, String> map = new HashMap<String, String>();
			map.put("status", "FAILD");
			ModelAndView mv = new ModelAndView(new XFMappingJacksonJsonView(), map);
			return mv;
		}else{
			ModelAndView mv = new ModelAndView("/destin/edit", Constants.FORM_COMMAND_KEY, form);
			mv.addObject("destin", destin);
			return mv;
		}		
	}
	
	@RequestMapping(value = "/doEdit")
	public ModelAndView doEditNotice(HttpServletRequest request, HttpServletResponse response, @Valid DestinForm form,
			BindingResult result) throws Exception {
		ModelAndView mv = new ModelAndView("/destin/edit_result", Constants.FORM_COMMAND_KEY, form);
		List<ObjectError> errorList = null;
		if (result.hasErrors()) {
			errorList = result.getAllErrors();
			// 将错误信息Json化
			String errorJson = errorToJson(errorList);
			mv.addObject("errorJson", errorJson);
			return mv;
		}
		if(StringUtil.StringFilter(form.getProv()).equals("") || StringUtil.StringFilter(form.getCity()).equals("")
				|| StringUtil.StringFilter(form.getAddress()).equals("") || StringUtil.StringFilter(form.getAreaname()).equals("")) {
			mv.addObject("status","FAILD");
			mv.addObject("msg","省、市、详细地址、目的地为必填项");
		}
		Map<String,Object> map = destinService.updateById(form);
		if(map.get("resu").equals("0")){
			mv.addObject("status","FAILD");
			mv.addObject("msg","服务器出错");			
		}else if(map.get("resu").equals("-1")){
			mv.addObject("status","FAILD");
			mv.addObject("msg","目的地超区");
		}else{
			mv.addObject("status", "SUCCESS");				
		}		
		return mv;
	}
	
	public String toJson(List<Map<Object,Object>> list){
		String dataJson = JSONArray.fromObject(list).toString();
		return dataJson;
		
	}
	
	/**
	 * 批量添加excel表记录
	 */
	@RequestMapping(value = "/addExcel")
	public ModelAndView add(HttpServletRequest request, HttpServletResponse response, DestinForm form) throws Exception {
		ModelAndView mv = new ModelAndView("/destin/excel_add", Constants.FORM_COMMAND_KEY, form);
		return mv;
	}
	
	@RequestMapping(value = "/doAddExcel")
	public ModelAndView doAddExcel(HttpServletRequest request, HttpServletResponse response, @Valid DestinForm form,
			BindingResult result) throws Exception {
		ModelAndView mv = new ModelAndView("/destin/excel_add_result", Constants.FORM_COMMAND_KEY, form);
		
		//1.错误检查
		List<ObjectError> errorList = null;
		if (result.hasErrors()) {
			errorList = result.getAllErrors();
			String errorJson = errorToJson(errorList);
			mv.addObject("errorJson", errorJson);
			return mv;
		}
		// 2.提取临时文件中的文件
		// 2.1空值检查
		/*String filename = form.getExcelFile().getOriginalFilename();
		String fileType = form.getExcelFile().getContentType();*/
		List<List<Map<String, String>>> list =null;
		try {
			list = ExcelUtils.readExcelWithTitle(form.getExcelFile());
		} catch (Exception e) {
			mv.addObject("status","FAILD");
			mv.addObject("msg",e.getMessage());
			return mv;
		}
		int count = 0;//总记录条数
		int noCount = 0;//失败条数
		String repeat ="记录地址重复的行号：<br/>";
		int rCount = 0;//重复记录条数
		String faild = "未知失败或省市错误行号：<br/>";
		int fCount = 0;//未知失败条数
		String lack ="省份|详细地址|目的地|没有数据其为行号：<br/>";
		int lCount = 0;//缺少字段条数
		String derror ="目的地超区行号：<br/>";
		int dCount = 0;//目的地知识条数
		DestinForm indest = new DestinForm();
		for (int i = 0; i < 1; i++) {
			List<Map<String,String>> listMap = list.get(i);
			excelCount = listMap.size();
			for (int j = 0; j < 500 && j < listMap.size(); j++) {
				handleCount = j+1;
				Map<String,String> map = listMap.get(j); 
				if(count == 0){				
					String str = map.keySet().toString();
					if(!str.contains("省份") || !str.contains("城市") || !str.contains("地区") || !str.contains("详细地址") || !str.contains("目的地")) {
						mv.addObject("status","FAILD");
						mv.addObject("msg","列标题名字错误");
						return mv;
					}
				}
				count++;
				String prov = map.get("省份")+"";
				String city = map.get("城市")+"";
				String area = map.get("地区")+"";
				String address = map.get("详细地址")+"";
				String areaname = map.get("目的地")+"";
				if(prov.equals("") || address.equals("") || areaname.equals("")){
					noCount ++;
					lack += (count+1)+",";
					if(lCount !=0 && lCount % 10 ==0){
						lack += "<br/>";
					};
					lCount++;
					continue;
				}
				indest.setSiteid(form.getSiteid());
				indest.setUserid(form.getUserid());
				indest.setProv(prov);
				indest.setCity(city);
				indest.setArea(area);
				indest.setAddress(address);
				indest.setAreaname(areaname);
				@SuppressWarnings("unused")
				Map<String,Object> mapreus = destinService.insertCheck(indest);
				if(map.get("resu") == "0"){
					noCount++;
					repeat += (count+1)+",";
					if(rCount !=0 && rCount % 10 ==0){
						repeat += "<br/>";
					};
					rCount++;
				}else if(map.get("resu") == "-1"){
					noCount++;
					faild += (count+1)+",";
					if(fCount !=0 && fCount % 10 ==0){
						lack += "<br/>";
					};
					fCount++;
				}else if(map.get("resu") == "-2"){
					noCount++;
					derror += (count+1)+",";
					if(dCount !=0 && dCount % 10 ==0){
						derror += "<br/>";
					};
					dCount++;
				}
			}	
			
		}
		
		mv.addObject("status","SUCCESS");
		mv.addObject("msg","总记录："+count+"，失败记录："+noCount+"<br/>"
				+"其中EXCEL表中记录失败原因：<br/>"+repeat+"<br/>"+derror+"<br/>"
				+lack+"<br/>"+faild+"<br/>"+"可以单条手动添加");		
		return mv;
	}
	
	@RequestMapping(value = "/getCound")
	@ResponseBody
	public String getCound(HttpServletRequest request, HttpServletResponse response)
			throws Exception {
		Map<String, Integer> map = new HashMap<String, Integer>();		
		map.put("excelCount", excelCount);
		map.put("handleCount", handleCount);
		String result = JSONObject.fromObject(map).toString();
		return result;
	}
	@RequestMapping("/getProv")
	@ResponseBody
	public List<Map<String, Object>> getProv(HttpServletRequest request, HttpServletResponse response) {
		return chinaareaService.getProv();
	}
	
	@RequestMapping("/getCity")
	@ResponseBody
	public List<Map<String, Object>> getCity(HttpServletRequest request, HttpServletResponse response, String prov) {
		return chinaareaService.getCity(prov);
	}
	
	@RequestMapping("/getArea")
	@ResponseBody
	public List<Map<String, Object>> getArea(HttpServletRequest request, HttpServletResponse response, String city) {
		List<Map<String,Object>> list = new ArrayList<>();
		if(StringUtil.StringFilter(city).equals("")) {
			return list;
		}else {
			list = chinaareaService.getArea(city);
		}
		return list;
	}
	
	@RequestMapping(value = "/query")
	public ModelAndView query(HttpServletRequest request, HttpServletResponse response, DestinForm form)
			throws Exception {
		ModelAndView mv = new ModelAndView("/destin/query", Constants.FORM_COMMAND_KEY, form);
		return mv;
	}
	
	@RequestMapping(value = "/doQuery")
	@ResponseBody
	public Map<String,Object> doQuery(HttpServletRequest request, HttpServletResponse response, @Valid DestinForm form
			,BindingResult result) throws Exception {
		String prov = StringUtil.StringFilter(form.getProv());
		String city = StringUtil.StringFilter(form.getCity());
		String area = StringUtil.StringFilter(form.getArea());
		String address = form.getAddress();
		Map<String, Object> map = new HashMap<String, Object>();
		List<ObjectError> errorList = null;
		if (result.hasErrors()) {
			errorList = result.getAllErrors();
			String errorJson = errorToJson(errorList);
			map.put("errorJson", errorJson);
			return map;
		}
		if(prov.equals("")) {
			FieldError e = new FieldError("", "prov", "省未选择，省为必填项");
			errorList = new ArrayList<ObjectError>();
			errorList.add(e);
			String errorJson = errorToJson(errorList);
			map.put("errorJson", errorJson);
			return map;
		}
		if(city.equals("")) {
			FieldError e = new FieldError("", "city", "市未选择，市为必填项");
			errorList = new ArrayList<ObjectError>();
			errorList.add(e);
			String errorJson = errorToJson(errorList);
			map.put("errorJson", errorJson);
			return map;
		}
		if(address !=null && address.equals("")) {
			FieldError e = new FieldError("", "address", "详细地址未填，详细地址为必填项");
			errorList = new ArrayList<ObjectError>();
			errorList.add(e);
			String errorJson = errorToJson(errorList);
			map.put("errorJson", errorJson);
			return map;
		}
		String resu = sendGetAreaname(prov, city, area, address);
		String areaname = null;		
		if(resu !=null) {
			JSONObject json = JSONObject.fromObject(resu);
			int status = json.getInt("status");
			String msg = json.getString("msg");
			if(status == 1) {
				areaname = json.getString("areaname");
			}else {
				FieldError e = new FieldError("", "areaname", msg);
				errorList = new ArrayList<ObjectError>();
				errorList.add(e);
				String errorJson = errorToJson(errorList);
				map.put("errorJson", errorJson);
				return map;
			}
		}
		if(areaname != null) {
			map.put("status", "OK");
			map.put("areaname", areaname);
		}
		return map;
	}
	@RequestMapping(value = "/queryExcel")
	public ModelAndView queryExcel(HttpServletRequest request, HttpServletResponse response, DestinForm form)
			throws Exception {
		ModelAndView mv = new ModelAndView("/destin/excel_query", Constants.FORM_COMMAND_KEY, form);
		return mv;
	}
	
	@RequestMapping(value = "/doQueryExcel")
	public ModelAndView queryExcel(HttpServletRequest request, HttpServletResponse response, @Valid DestinForm form,
			BindingResult result) throws Exception {
		ModelAndView mv = new ModelAndView("/destin/excel_query_result", Constants.FORM_COMMAND_KEY, form);
		//0.错误检查
		List<ObjectError> errorList = null;
		if (result.hasErrors()) {
			errorList = result.getAllErrors();
			String errorJson = errorToJson(errorList);
			mv.addObject("errorJson", errorJson);
			return mv;
		}
		//1.建一个空excel文件
		SimpleDateFormat formatter = new SimpleDateFormat("yy年MM月dd日HH时mm分");
		String nowDateStr = formatter.format(new Date());
		String sheetName = nowDateStr+"批量查询目的地";
		String[] title= {"单号","省份","城市","地区","详细地址","目的地"};
		// 1.1创建一个workbook，对应一个Excel文件
		@SuppressWarnings("resource")
		final HSSFWorkbook wb = new HSSFWorkbook();
	    // 1.2.在workbook中添加一个sheet，对应Excel中的一个sheet
	    final HSSFSheet sheet = wb.createSheet(sheetName);
	    // 1.3.在sheet中添加表头第0行，老版本poi对excel行数列数有限制short
	    //锁定第一行
	    sheet.createFreezePane( 0, 1, 0, 1 );
	    HSSFRow row = sheet.createRow((int) 0);
	    // 1.4.创建单元格，设置值表头，设置表头居中
	    HSSFCellStyle style = wb.createCellStyle();
	    // 居中格式
	    style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
	    for (int i = 0; i < title.length; i++) {    
            HSSFCell cell = row.createCell(i);    
            cell.setCellValue(title[i]);    
            cell.setCellStyle(style);    
            sheet.setColumnWidth(i, 5*2*256);
        }
		// 2.提取临时文件中的文件
		// 2.1空值检查
		List<List<Map<String, String>>> list =null;		
		try {
			list = ExcelUtils.readExcelWithTitle(form.getExcelFile());
		} catch (Exception e) {
			mv.addObject("status","FAILD");
			mv.addObject("msg",e.getMessage());
			return mv;
		}
		//3创建线程
		int cpuCount = Runtime.getRuntime().availableProcessors();
		ExecutorService exe = Executors.newFixedThreadPool(cpuCount*2);
		for (int i = 0; i < 1; i++) {			
			List<Map<String,String>> listMap = list.get(i);
			queryCount = listMap.size();
			queryRunCount = 0;
			for (int j = 0; j < listMap.size(); j++) {				
				final Map<String, String> map = listMap.get(j);
				if(j == 0) {
					String str = map.keySet().toString();
					if(!str.contains("省份") || !str.contains("城市") || !str.contains("地区") || !str.contains("详细地址")) {
						mv.addObject("status","FAILD");
						mv.addObject("msg","列标题名字错误");
						return mv;
					}				
				}
				final int s = j;
				exe.execute(new Runnable() {				
					public void run() {
						HSSFRow row = sheet.getRow((int) 0);
						//建立一行记录
						row = sheet.createRow(s + 1);
						String code = map.get("单号")!=null?map.get("单号"):"";
						String inProv = StringUtil.StringFilter(map.get("省份")+"");
						String inCity = StringUtil.StringFilter(map.get("城市")+"");
						String inArea = StringUtil.StringFilter(map.get("地区")+"");
						String inAddress = map.get("详细地址").replaceAll(" ", "");
						if(inProv.equals(inCity)) {
							inCity = inArea;
							inArea = "";
						}
						String prov = CheckChinaArea.checkProv(inProv);
						String city ="";
						String area ="";
						JSONObject json = null;
						if(!prov.equals("")) {
							List<Tbchinaarea> cityList = chinaareaService.ProvAndCity(prov);
							city = CheckChinaArea.checkCity(inCity, cityList);
						}
						if(!city.equals("")) {
							List<Tbchinaarea> areaList = chinaareaService.CityAndArea(city);
							area = CheckChinaArea.checkArea(inArea, areaList);
						}						
						String address = inAddress.replaceAll(inProv, "").replaceAll(inCity, "").replaceAll(inArea, "");
						if(!prov.equals("") || !city.equals("") || !address.equals("")) {
							String jsonStr = sendGetAreaname(prov, city, area, address);
							json = JSONObject.fromObject(jsonStr);
						}						
						String areaname = "";
						if(json !=null && json.getInt("status") == 1) {
							areaname = json.getString("areaname");
						}
						row.createCell(0).setCellValue(code);
			            row.createCell(1).setCellValue(prov);
			            row.createCell(2).setCellValue(city);
			            row.createCell(3).setCellValue(area);
			            row.createCell(4).setCellValue(address);
			            row.createCell(5).setCellValue(areaname);
			            queryRunCount++;		            
					}
				}); 				
			}
		}
		boolean b = false;
		exe.shutdown(); 
        while (true) { 
            if (exe.isTerminated()) { 
                b = true; 
                break; 
            }
            Thread.sleep(200);
        }
		if(b) {
			response.setContentType("application/vnd.ms-excel;charset=utf-8");
			response.setHeader("Content-Disposition", "attachment;filename="
					+ new String((sheetName + ".xls").getBytes(), "iso-8859-1"));
			OutputStream ouputStream = response.getOutputStream();    
			wb.write(ouputStream);    
			ouputStream.flush();    
			ouputStream.close();
			return null;
		}
		return mv;
	}
	
	@RequestMapping(value = "/getQueryCound")
	@ResponseBody
	public String getQueryCound(HttpServletRequest request, HttpServletResponse response)
			throws Exception {
		Map<String, Integer> map = new HashMap<String, Integer>();		
		map.put("queryCount", queryCount);
		map.put("queryRunCount", queryRunCount);
		String result = JSONObject.fromObject(map).toString();
		return result;
	}
	
	public static String sendGetAreaname(String prov,String city,String area,String address){
		try {
			prov = URLEncoder.encode(prov,"UTF-8");
			city = URLEncoder.encode(city,"UTF-8");
			area = URLEncoder.encode(area,"UTF-8");
			address = URLEncoder.encode(address,"UTF-8");
			String param = "action=site&token=286HF442&province="+prov+"&city="+city+"&area="+area+"&address="+address;
			String json = null;
			String URL = "http://www.xf-express.com.cn:8079/api/dest.ashx";
			String url = URL+"?"+param;
			json = HttpTool.httpRequest(url);
			return json;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;		
	}
	
	
}
